IF OBJECT_ID('ssf.ptx_get_task_group_dependency_level') IS NOT NULL
	DROP FUNCTION ssf.ptx_get_task_group_dependency_level
GO

CREATE FUNCTION ssf.ptx_get_task_group_dependency_level (
	@task_group_id INT
)
RETURNS @ptx_dependency_level TABLE (
	dependency_level INT NOT NULL
	, task_id INT NOT NULL
)
AS
BEGIN
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

	DECLARE	@row_count INT
				, @dependency_level INT = 0

	INSERT @ptx_dependency_level
	SELECT @dependency_level
		, t.task_id
	FROM ssf.ptx_get_active_task(@task_group_id) t
	OUTER APPLY ssf.ptx_get_active_task_parents(t.task_id) d
	WHERE d.task_id IS NULL

	SET @row_count = @@ROWCOUNT

	WHILE @row_count > 0
	BEGIN
		SET @dependency_level += 1
		
		INSERT @ptx_dependency_level
		SELECT @dependency_level
			, t.task_id
		FROM ssf.ptx_get_active_task(@task_group_id) t
		WHERE NOT EXISTS (
			SELECT d.task_id
			FROM ssf.ptx_get_active_task_parents(t.task_id) d
			WHERE d.task_id NOT IN (SELECT task_id FROM @ptx_dependency_level)
		)
		AND t.task_id NOT IN (SELECT task_id FROM @ptx_dependency_level)

		SET @row_count = @@ROWCOUNT
	END

	RETURN
END
GO
